# -*- coding: UTF-8 -*-
import datetime
from sqlalchemy import create_engine, MetaData
from sqlalchemy import Table, Column, Integer, String, ForeignKey, types
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select

from pylons import config

Session=sessionmaker(autoflush=True, transactional=True)
metadata = MetaData()

types_mapper = {
                'integer': types.Integer,
                'string': types.String,
                'unicode': types.Unicode,
                'float': types.Float,
                'date': types.Date,
                'datetime': types.DateTime,
                'timestamp': types.TIMESTAMP,
                'numeric': types.Numeric,
                'boolean': types.Boolean,
                'text': types.TEXT,
                'binary': types.Binary
                }

class ExistingTableException(Exception): pass
class TableNotExistException(Exception): pass
class UnknownTypeException(Exception): pass

class Database:
    def __init__(self):
        ### Connect to Database
        self.engine = create_engine(config.get('sqlalchemy.default.url'), echo=True)
        ### Create MetaData instance
        self.metadata = metadata
        self.metadata.bind = self.engine
        self.tables={}
        self._loadTables()
        self.session = None

    def _StartOperation(self):
        self.session = Session(bind=self.engine)
        self.session.bind.connect()
        self.session.begin()

    def _EndOperation(self):
        self.session.flush()
        self.session.close()
        self.session.clear()

    def createTable(self, table, fields=None):
        name = table['name']
        if not self.tables.has_key(name):
            ### Define Table on MetaData
            self.tables[name] = Table(name, self.metadata)
            self.tables[name].append_column(Column("id", types.Integer, primary_key=True, autoincrement=True))
            for field in fields:
                if not types_mapper.has_key(field['type']):
                    raise UnknownTypeException(field['type'])
                kwargs = {}
                if field.get('primarykey') is not None:
                    kwargs['primary_key'] = True
                if field.get('autoincrement') is not None:
                    kwargs['autoincrement'] = True
                if field.get('default') is not None:
                    kwargs['default'] = field.get('default')
                if field.get('unique') is not None:
                    kwargs['unique'] = True
                if field.get('nullable') is not None:
                    kwargs['nullable'] = False
                if field.get('index') is not None:
                    kwargs['index'] = True
                column = Column(field['name'], types_mapper[field['type']], **kwargs)
                self.tables[name].append_column(column)
            if not table.has_key('created') or table['created'] != 'false':
                self.tables[name].append_column(Column("created", types.DateTime, default=datetime.datetime.now()))
            if not table.has_key('updated') or table['updated'] != 'false':
                self.tables[name].append_column(Column("updated", types.DateTime, onupdate=datetime.datetime.now()))
            ### Save MetaData on Database
            self.tables[name].create(bind=self.engine)#session.bind)#, checkfirst=True)
        else:
            raise ExistingTableException(name)

    def dropTable(self, name):
        if self.tables.has_key(name):
            self._StartOperation()
            ### Drop Table
            self.tables.get(name).drop(bind=self.session.bind)#, checkfirst=True)
            self.metadata.remove(self.tables[name])
            ### Delete table from dictionary
            del self.tables[name]
            self._EndOperation()

    def dropAllTables(self):
        self._StartOperation()
        ### Drop Table
        self.metadata.drop_all(bind=self.session.bind)
        self.metadata.clear()
        ### Delete table from dictionary
        self.tables = {}
        self._EndOperation()

    def _loadTables(self):
        self._StartOperation()
        self.metadata.bind = self.engine
        self.tables = dict([(t.name, t) for t in self.metadata.table_iterator(reverse=False)])
        self._EndOperation()

    def reloadTables(self):
        self._loadTables()

    def _countTables(self):
        self._StartOperation()
        self.metadata.bind = self.engine
        i = 0
        for t in self.metadata.table_iterator(reverse=False):
            i += 1
        self._EndOperation()
        return i

    def getRecords(self, table):
        if self.tables.has_key(table):
            s = select([self.tables[table]])
            r = self.engine.execute(s)
            columns = self.getColumns(table)
            result = []
            for row in r:
                field = {}
                for col in columns:
                    field[col] = row[col]
                result.append(field)
            return result
        else:
            raise TableNotExistException(table)

    def insertRecord(self, table, fields):
        if self.tables.has_key(table):
            ins = self.tables[table].insert(values=fields)
            result = self.engine.execute(ins)
            return result.last_inserted_ids()[0]
        else:
            raise TableNotExistException(table)

    def getColumns(self, table):
        if self.tables.has_key(table):
            self._StartOperation()
            return [t.name for t in self.tables[table].columns]
            self._EndOperation()
        else:
            raise TableNotExistException(table)

    def getDefinition(self, table):
        if self.tables.has_key(table):
            return {'name': table, 'fields': [t.name for t in self.tables[table].columns]}
        else:
            raise TableNotExistException(table)

    def updateRecord(self, table, id, fields):
        if self.tables.has_key(table):
            table = self.tables[table]
            update = table.update(table.c.id == id, values=fields)
            self.engine.execute(update)
        else:
            raise TableNotExistException(table)

    def deleteRecord(self, table, id):
        if self.tables.has_key(table):
            table = self.tables[table]
            delete = table.delete(table.c.id == id)
            self.engine.execute(delete)
        else:
            raise TableNotExistException(table)
#
#    def recordExists(self, table, id):
#        if self.tables.has_key(table):
#            ins = self.tables[table].insert(values=fields)
#            result = self.engine.execute(ins)
#        else:
#            raise TableNotExistException(table)